package com.ttl.bc.ap.purchaseorder.db;

import hibernate.util.HibernateUtil;

import java.util.Iterator;
import java.util.List;

import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.classic.Session;


import com.ttl.bc.ap.purchaseorder.bean.BCPurchaseOrderBean;
import com.ttl.bc.ap.purchaseorder.bean.BCPurchaseOrderSubBean;

import com.ttl.bc.ap.stkrequest.BCStkRequestBean;
import com.ttl.bc.ap.stkrequest.BCStkRequestSubBean;
import com.ttl.bc.ap.stkrequest.BCStkRequestSubShowBean;
import com.ttl.common.db.ADAO;

public class BCPurchaseOrderDAO extends ADAO {

	@SuppressWarnings("unchecked")
	public void delete(String docNo) {
		Session session = null;

		try {
			sessionFactory = HibernateUtil.getSessionFactory();
			 session = sessionFactory.openSession();
			session.beginTransaction();

            String hql = "FROM BCPurchaseOrderBean h where h.docNo = '"+docNo+"'";
            org.hibernate.Query q = session.createQuery(hql);
            List results = q.list();			

            if(results!=null) {
	            for(int i=0; i<results.size(); i++){
	            	BCPurchaseOrderBean h = (BCPurchaseOrderBean)results.get(i);
	            	h.setIsCancel(1);
	            	
	            	Iterator it = h.getBcPurchaseOrderSubs().iterator();
	            	while (it.hasNext()) {
						BCPurchaseOrderSubBean sub = (BCPurchaseOrderSubBean) it.next();
						sub.setIsCancel(1);
					}
	                session.update(h);
	            }
            }
            
			session.getTransaction().commit();
			
        } catch (HibernateException he) {
            he.printStackTrace();        
		}finally {
			session.close();
		}
	}

	@SuppressWarnings("unchecked")
	@Override
	public boolean update(String s, Object o) {
		Session session = null;

		try {
			sessionFactory = HibernateUtil.getSessionFactory();
			 session = sessionFactory.openSession();
			session.beginTransaction();

			BCPurchaseOrderBean poNew = (BCPurchaseOrderBean)o;
			
            String hql = "FROM BCPurchaseOrderBean h where h.docNo = '"+s+"'";
           // System.out.println(hql);	
            org.hibernate.Query q = session.createQuery(hql);
            List resultList = q.list();			

            for(int i=0; i<resultList.size(); i++){
            	BCPurchaseOrderBean poOld = (BCPurchaseOrderBean)resultList.get(i);
            	
            	Iterator it = poOld.getBcPurchaseOrderSubs().iterator();
            	while (it.hasNext()) {
					BCPurchaseOrderSubBean poSubOld = (BCPurchaseOrderSubBean) it.next();
					
					Iterator itPo = poNew.getBcPurchaseOrderSubs().iterator();
					while (itPo.hasNext()) {
						BCPurchaseOrderSubBean poSubNew = (BCPurchaseOrderSubBean) itPo.next();
						
				//		System.out.println("poSubOld.getIttl_rowOrderOfStkReq().intValue()="+poSubOld.getIttl_rowOrderOfStkReq().intValue());
			//			System.out.println("poSubNew.getIttl_rowOrderOfStkReq().intValue()="+poSubNew.getIttl_rowOrderOfStkReq().intValue());
	//					System.out.println("\n\n\n");
														
						//ถ้าเลข Ittl_rowOrderOfStkReq ของ po เดิม ไม่เท่ากับ ของ po ใหม่ จริง : 
						//ให้ไปหาว่า  "poSubOld.getIttl_rowOrderOfStkReq().intValue() นั้นอยู่ใน PO อื่นๆอีกหรือไม่ (กรณีสร้าง PO จาก  PO"
						if(poSubOld.getIttl_rowOrderOfStkReq()!=null &&	poSubNew.getIttl_rowOrderOfStkReq()!=null &&
								poSubOld.getIttl_rowOrderOfStkReq().intValue()!=poSubNew.getIttl_rowOrderOfStkReq().intValue() /*&&
								!poOld.getDocNo().equalsIgnoreCase(poNew.getDocNo())*/ ) {
							
//							hql = "FROM BCPurchaseOrderSubShowBean WHERE ittl_rowOrderOfStkReq="+poSubOld.getIttl_rowOrderOfStkReq().intValue();
//							Query poQ = session.createQuery(hql);
//							List poList = poQ.list();
//							System.out.println(hql);
//							System.out.println("size="+poList.size());
							// ถ้า เจอ ittl_rowOrderOfStkReq = 1 ตัวใน BCPurchaseOrderSub แสดงว่ามีแค่ตัวเดียว update itemนั้น ใน bcstkRequestSub ให้ ittl_status=0ได้
							// ถ้าเจอ ittl_rowOrderOfStkReq >1 ตัว แสดงว่ามี PO ใบอื่นที่อ้าง item นี้อยู่ จะไม่ต้อง update: ittl_status
//							if(poList!=null && poList.size()==1) {
								
								hql = "FROM BCStkRequestSubShowBean WHERE rowOrder = "+poSubOld.getIttl_rowOrderOfStkReq().intValue();
								Query prQ = session.createQuery(hql);
								
								List prList = prQ.list();
							//	System.out.println(hql);
						//		System.out.println("size="+prList.size());
								
								if(prList!=null && prList.size()>0) {
									BCStkRequestSubShowBean stkReq = (BCStkRequestSubShowBean)prList.get(0);
									stkReq.setIttl_status("0");
									
									//System.out.println("update BCStkRequestSub");
									session.update(stkReq);
								}								
//							}
						}
					}
				}
            	
                session.delete(poOld);
            } 
			session.save(o);

			session.getTransaction().commit();
			return true;
        } catch (HibernateException he) {
            he.printStackTrace();
			return false;
		}finally {
			session.close();
		}
	}

	@SuppressWarnings("unchecked")
	public boolean updateStatusAfterSaveInvoice(String poRefNo) {
		Session session = null;

		try {
			sessionFactory = HibernateUtil.getSessionFactory();
			 session = sessionFactory.openSession();
			session.beginTransaction();

            String hql = "FROM BCPurchaseOrderBean h where h.docNo = '"+poRefNo+"'";
            
            org.hibernate.Query q = session.createQuery(hql);
            List resultList = q.list();			

            for(int i=0; i<resultList.size(); i++){
            	BCPurchaseOrderBean h = (BCPurchaseOrderBean)resultList.get(i);
            	h.setPoStatus(2);
            	h.setBillStatus(1);
                session.save(h);
            }                        
			
			session.getTransaction().commit();
			
			return true;
        } catch (HibernateException he) {
            he.printStackTrace();            
			return false;
		}finally {
			session.close();
		}
	}

		
	
	@SuppressWarnings("unchecked")
	public boolean updatepo(String docNo, BCPurchaseOrderBean po) {
		Session session = null;

		try {
			sessionFactory = HibernateUtil.getSessionFactory();
			 session = sessionFactory.openSession();
			session.beginTransaction();

            String hql = "FROM BCPurchaseOrderBean h where h.docNo = '"+docNo+"'";
            org.hibernate.Query q = session.createQuery(hql);
            List results = q.list();			

            if(results!=null) {
	            for(int i=0; i<results.size(); i++){
	            	BCPurchaseOrderBean h = (BCPurchaseOrderBean)results.get(i);	            	
	                session.delete(h);
	            }
            }
            session.save(po);
            
			session.getTransaction().commit();
			
			return true;
        } catch (HibernateException he) {
            he.printStackTrace();        
			return false;
		}finally {
			session.close();
		}
		
	}

	@SuppressWarnings("unchecked")
	public boolean deletepo(String docNo) {
		Session session = null;

		try {
			sessionFactory = HibernateUtil.getSessionFactory();
			 session = sessionFactory.openSession();
			session.beginTransaction();

            String hql = "FROM BCPurchaseOrderBean h where h.docNo = '"+docNo+"'";
            org.hibernate.Query q = session.createQuery(hql);
            List results = q.list();			

            if(results!=null) {
	            for(int i=0; i<results.size(); i++){
	            	BCPurchaseOrderBean h = (BCPurchaseOrderBean)results.get(i);
	            	h.setIsCancel(1);
	            	Iterator it = h.getBcPurchaseOrderSubs().iterator();
	            	while (it.hasNext()) {
						BCPurchaseOrderSubBean object = (BCPurchaseOrderSubBean) it.next();
						object.setIsCancel(1);
					}
	                session.update(h);
	            }
            }
            
            
			session.getTransaction().commit();
			
			return true;
        } catch (HibernateException he) {
            he.printStackTrace();        
			return false;
		}finally {
			session.close();
		}
	}

	@SuppressWarnings("unchecked")
	public boolean addPrintCount(String docNoParam) {
		Session session = null;

		try {
			sessionFactory = HibernateUtil.getSessionFactory();
			 session = sessionFactory.openSession();
			session.beginTransaction();

            String hql = "FROM BCPurchaseOrderBean po where "+docNoParam;
            org.hibernate.Query q = session.createQuery(hql);
            List results = q.list();			

            if(results!=null) {
	            for(int i=0; i<results.size(); i++){
	            	BCPurchaseOrderBean h = (BCPurchaseOrderBean)results.get(i);
	            	if(h.getPrintCount()!=null) { 
	            		h.setPrintCount(h.getPrintCount()+1);
	            	}else {
	            		h.setPrintCount(1);
	            	}
	            	session.save(h);
	            }
            }
            
			session.getTransaction().commit();
			
			return true;
        } catch (HibernateException he) {
            he.printStackTrace();        
			return false;
		}finally {
			session.close();
		}
		
	}

	@SuppressWarnings("unchecked")
	public boolean addPO(BCPurchaseOrderBean po) {
		Session session = null;

		try {
			sessionFactory = HibernateUtil.getSessionFactory();
			 session = sessionFactory.openSession();
			session.beginTransaction();
			
			Iterator it = po.getBcPurchaseOrderSubs().iterator();
			while (it.hasNext()) {
				BCPurchaseOrderSubBean psub = (BCPurchaseOrderSubBean) it.next();
				
				String hql = "FROM BCStkRequestBean WHERE docNo ='"+psub.getStkReqNo()+"' ";				
				org.hibernate.Query q = session.createQuery(hql);
	            List results = q.list();
	            for (int i = 0; i < results.size(); i++) {
	            	BCStkRequestBean s = (BCStkRequestBean)results.get(i);
	            	s.setIttl_status("1");
					Iterator itstk = s.getBcStkRequestSubs().iterator();
					
					while (itstk.hasNext()) {
						BCStkRequestSubBean sub = (BCStkRequestSubBean) itstk.next();
						if(psub.getIttl_rowOrderOfStkReq()!=null &&
								sub.getRowOrder().intValue()==psub.getIttl_rowOrderOfStkReq().intValue()) {
							sub.setIttl_status("1");// 0=ขอซื้อ, 1=ออกใบ po แล้ว, 2=ซื้อแล้ว
							sub.setRemainQty(0f);
						}						
					}
					System.out.println("save bcstkrequest");
					session.save(s);
					
					if(!po.getTempMyDesc5().equals("[STOCK]")) {
						po.setTempMyDesc3(s.getIttl_deptRequest()+"   "+s.getDepartCode()+"   "+s.getProjectCode());
					}
				}
				hql = "FROM BCReqConfirmBean WHERE docNo ='"+psub.getConfirmNo()+"' ";
			}
			session.save(po);
			
			session.getTransaction().commit();

			return true;
		}catch(Exception e) {
			System.out.println(e.toString());
			return false;
		}finally {
			session.close();
		}
	}

	@SuppressWarnings("unchecked")
	public boolean updatePO(String docNo, BCPurchaseOrderBean po) {
		Session session = null;

		try {
			sessionFactory = HibernateUtil.getSessionFactory();
			 session = sessionFactory.openSession();
			session.beginTransaction();
			
			String hql = "FROM BCPurchaseOrderBean WHERE docNo ='"+docNo+"' ";				
			org.hibernate.Query q = session.createQuery(hql);
            List results = q.list();
            if(results!=null && results.size()>0) {
            	for (int i = 0; i < results.size(); i++) {
            		BCPurchaseOrderBean poOld = (BCPurchaseOrderBean)results.get(i);
					Iterator itOld = poOld.getBcPurchaseOrderSubs().iterator();
					while (itOld.hasNext()) {
						BCPurchaseOrderSubBean subOld = (BCPurchaseOrderSubBean) itOld.next();
						
						hql = "FROM BCStkRequestBean WHERE docNo ='"+subOld.getStkReqNo()+"' ";
						org.hibernate.Query qStk = session.createQuery(hql);
			            List resultsStk = qStk.list();
			            if(resultsStk!=null && resultsStk.size()>0) {
			            	for (int i2 = 0; i2 < resultsStk.size(); i2++) {
			            		BCStkRequestBean stk = (BCStkRequestBean)resultsStk.get(i2);
			            		Iterator itStk = stk.getBcStkRequestSubs().iterator();
			            		while (itStk.hasNext()) {
									BCStkRequestSubBean stkSub = (BCStkRequestSubBean) itStk.next();
									if(subOld.getIttl_rowOrderOfStkReq()!=null && 
											subOld.getIttl_rowOrderOfStkReq().intValue()==stkSub.getRowOrder().intValue()) {
							
										stkSub.setRemainQty(stkSub.getQty());
										stkSub.setIttl_status("0");										
									}
								}
			            		session.save(stk);
			            	}
			            }			            
					}
		    		session.delete(poOld);
				}
            }
            //end
            
			Iterator it = po.getBcPurchaseOrderSubs().iterator();
			while (it.hasNext()) {
				BCPurchaseOrderSubBean psub = (BCPurchaseOrderSubBean) it.next();
				
				hql = "FROM BCStkRequestBean WHERE docNo ='"+psub.getStkReqNo()+"' ";				
				org.hibernate.Query q2 = session.createQuery(hql);
	            results = q2.list();
	            for (int i = 0; i < results.size(); i++) {
	            	BCStkRequestBean s = (BCStkRequestBean)results.get(i);
					Iterator itstk = s.getBcStkRequestSubs().iterator();
					
					while (itstk.hasNext()) {
						BCStkRequestSubBean sub = (BCStkRequestSubBean) itstk.next();
						if(psub.getIttl_rowOrderOfStkReq()!=null &&
								sub.getRowOrder().intValue()==psub.getIttl_rowOrderOfStkReq().intValue()) {
							sub.setQty(psub.getQty());
							sub.setIttl_status("1");// 0=ขอซื้อ, 1=ออกใบ po แล้ว, 2=ซื้อแล้ว
							sub.setRemainQty(0f);
						}						
					}
					System.out.println("save bcstkrequest");
					session.save(s);
					
					if(!po.getTempMyDesc5().equals("[STOCK]")) {
						po.setTempMyDesc3(s.getIttl_deptRequest()+"   "+s.getDepartCode()+"   "+s.getProjectCode());
					}
				}
				hql = "FROM BCReqConfirmBean WHERE docNo ='"+psub.getConfirmNo()+"' ";
			}
			session.save(po);
			
			session.getTransaction().commit();

			return true;
		}catch(Exception e) {
			System.out.println(e.toString());
			return false;
		}finally {
			session.close();
		}
	}

	@SuppressWarnings("unchecked")
	public boolean deletePO(String docNo) {
		Session session = null;

		try {
			sessionFactory = HibernateUtil.getSessionFactory();
			 session = sessionFactory.openSession();
			session.beginTransaction();
			
			String hql = "FROM BCPurchaseOrderBean WHERE docNo ='"+docNo+"' ";				
			org.hibernate.Query q = session.createQuery(hql);
            List results = q.list();
            if(results!=null && results.size()>0) {
            	for (int i = 0; i < results.size(); i++) {
            		BCPurchaseOrderBean poOld = (BCPurchaseOrderBean)results.get(i);
					Iterator itOld = poOld.getBcPurchaseOrderSubs().iterator();
					while (itOld.hasNext()) {
						BCPurchaseOrderSubBean subOld = (BCPurchaseOrderSubBean) itOld.next();
						
						hql = "FROM BCStkRequestBean WHERE docNo ='"+subOld.getStkReqNo()+"' ";
						org.hibernate.Query qStk = session.createQuery(hql);
			            List resultsStk = qStk.list();
			            if(resultsStk!=null && resultsStk.size()>0) {
			            	for (int i2 = 0; i2 < resultsStk.size(); i2++) {
			            		BCStkRequestBean stk = (BCStkRequestBean)resultsStk.get(i2);
			            		
			            		boolean found1_Ittl_status = false;
			            		Iterator itStk = stk.getBcStkRequestSubs().iterator();
			            		while (itStk.hasNext()) {
									BCStkRequestSubBean stkSub = (BCStkRequestSubBean) itStk.next();
									if(subOld.getIttl_rowOrderOfStkReq()!=null && 
											subOld.getIttl_rowOrderOfStkReq().intValue()==stkSub.getRowOrder().intValue()) {
							
										stkSub.setRemainQty(stkSub.getQty());
										stkSub.setIttl_status("0");										
									}
									if(stkSub.getIttl_status().equals("1")) {
										found1_Ittl_status = true;
									}
								}
			            		
			            		if(found1_Ittl_status == false) {
			            			stk.setIttl_status("0");
			            		}
			            		
			            		session.save(stk);
			            	}
			            }
			            subOld.setIsCancel(1);
					}
					poOld.setIsCancel(1);
		    		session.save(poOld);
				}
            }
            
			session.getTransaction().commit();

			return true;
		}catch(Exception e) {
			System.out.println(e.toString());
			return false;
		}finally {
			session.close();
		}
	}
}
